package dao.service;

import dao.BaseDao;
import dao.UserDao;
import modle.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author DELL
 */
public class UserDaoImp implements UserDao {

    @Override
    public boolean delUser(String userID) {
        String sql = "delete from user where user_id=?";
        Object[] params = {userID};

        if (BaseDao.executedOperation(sql, params) != 0) {
            return true;
        }
        return false;
    }

    @Override
    public Boolean update(User user) {
        String sql = "update user set user_name=?,user_address=?,user_birthday=DATE_FORMAT(?,'%Y-%m-%d')," +
                "user_email=?,user_password=?,user_phone=?,user_sex=? where user_id = ?";
        Object[] params = {
                user.getUser_name(),
                user.getUser_address(),
                user.getUser_birthday(),
                user.getUser_email(),
                user.getUser_password(),
                user.getUser_phone(),
                user.getUser_sex(),

                //不可修改的属性
                user.getUser_id(),
                //user.getUser_identity_code(),
                //user.getUser_status()
        };

        if (BaseDao.executedOperation(sql, params) > 0) {
            return true;
        }
        return false;
    }

    @Override
    public User getUser(String id) {
        User user = null;
        ResultSet resultSet = null;
        PreparedStatement ps = null;
        Connection connection = BaseDao.getConnection();

        String sql = "select * from user where user_id=? group by user_id";

        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, id);
            resultSet = ps.executeQuery();

            while (resultSet.next()) {
                user = new User(
                        resultSet.getString(1),
                        resultSet.getString(2),
                        resultSet.getString(3),
                        resultSet.getString(4),
                        resultSet.getString(5),
                        resultSet.getString(6),
                        resultSet.getString(7),
                        resultSet.getString(8),
                        resultSet.getString(9),
                        resultSet.getInt(10)
                );
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.closeConnection(resultSet, ps, connection);
        }

        return user;
    }

    @Override
    public int insert(User user) {
        String sql = "insert into user values(?,?,?,?,DATE_FORMAT(?,'%Y-%m-%d'),?,?,?,?,?)";

        Object[] params = {
                user.getUser_id(),
                user.getUser_name(),
                user.getUser_password(),
                user.getUser_sex(),
                user.getUser_birthday(),
                user.getUser_identity_code(),
                user.getUser_email(),
                user.getUser_phone(),
                user.getUser_address(),
                user.getUser_status()
        };

        return BaseDao.executedOperation(sql, params);
    }


    @Override
    public int getNumOfUsers(String keywords) {
        int totalNum = 0;

        Connection c = BaseDao.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "select count(*) from user where user_name like ?";

        try {
            ps = c.prepareStatement(sql);
            //避免sql注入
            ps.setString(1, "%" + keywords + "%");

            rs = ps.executeQuery();
            while (rs.next()) {
                totalNum = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.closeConnection(rs, ps, c);
        }


        return totalNum;
    }

    @Override
    public int getNumOfUsers() {
        int totalNum = 0;

        Connection c = BaseDao.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        String sql = "select count(*) from user";

        try {
            ps = c.prepareStatement(sql);
            rs = ps.executeQuery();

            //在获取结果集结果之前必须使用.next方法
            while (rs.next()) {
                totalNum = rs.getInt(1);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.closeConnection(rs, ps, c);
        }

        return totalNum;
    }

    @Override
    public ArrayList<User> getUserList(int page, int count) {
        ArrayList<User> users = new ArrayList<>();
        ResultSet resultSet;

        Connection connection = BaseDao.getConnection();

        PreparedStatement ps = null;
        String sql = "select * from user order by user_id desc limit ?, ?";

        try {
            ps = connection.prepareStatement(sql);
            //给sql语句中的两个问号赋值
            ps.setInt(1, (page - 1) * count);
            ps.setInt(2, count);

            executeSql(users, ps);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return users;
    }

    @Override
    public ArrayList<User> getUserList(String keywords, int page, int count) {
        ArrayList<User> users = new ArrayList<>();

        ResultSet resultSet;
        Connection connection = BaseDao.getConnection();

        PreparedStatement ps = null;

        /*容易被sql注入
        String sql = "select * from user where user_name like '%" + keywords + "%' " +
                " order by user_id desc limit ?, ?";*/
        String sql = "select * from user where user_name like ? order by user_id desc limit ?, ?";

        try {
            ps = connection.prepareStatement(sql);
            //避免sql注入
            ps.setString(1, "%" + keywords + "%");
            ps.setInt(2, (page - 1) * count);
            ps.setInt(3, count);

            executeSql(users, ps);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return users;
    }

    /**
     * sql块提取
     *
     * @param users 用户列表
     * @param ps    预处理块
     * @throws SQLException
     */
    private void executeSql(ArrayList<User> users, PreparedStatement ps) throws SQLException {
        ResultSet resultSet;
        resultSet = ps.executeQuery();

        while (resultSet.next()) {
            User user = new User(
                    resultSet.getString(1),
                    resultSet.getString(2),
                    resultSet.getString(3),
                    resultSet.getString(4),
                    resultSet.getString(5),
                    resultSet.getString(6),
                    resultSet.getString(7),
                    resultSet.getString(8),
                    resultSet.getString(9),
                    resultSet.getInt(10)
            );
            users.add(user);
        }
    }
}
